package com.xielx.ris.statistic.db;

import com.xielx.ris.statistic.bean.ReportRow;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.joda.time.DateTime;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Random;

public class DbUtil {
    private final static String DRIVER_CLASS_NAME="oracle.jdbc.driver.OracleDriver";
    private final static String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
//    private final static String USER_NAME = "mpp_user_6b";
//    private final static String PASSWORD = "mpp_user_6b";

    private final static String USER_NAME = "net_rural";
    private final static String PASSWORD = "net_rural";
//private final static String USER_NAME = "net_rural_gab";
//    private final static String PASSWORD = "net_rural_gab";

    private final static DataSource DATA_SOURCE;
    private final static JdbcTemplate jdbcTemplate;
    private final static String SQL = "insert into mpp_user_6b.mcl_tpl_country_mon_total_2020 (TJRQ, ZH_SF, SJLX, TJSL, GXSJ)"+
            "values(?, ?, ?, ?, ?)";

    public static JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    static {
        DATA_SOURCE = new DataSource();
        DATA_SOURCE.setUrl(URL);
        DATA_SOURCE.setDriverClassName(DRIVER_CLASS_NAME);
        DATA_SOURCE.setUsername(USER_NAME);
        DATA_SOURCE.setPassword(PASSWORD);
        DATA_SOURCE.setDefaultAutoCommit(true);
        jdbcTemplate = new JdbcTemplate(DATA_SOURCE);
    }

    public static void insert(List<ReportRow> list) {
        List<Object[]> argsList = new ArrayList<>(list.size());
        for (ReportRow row : list) {
            argsList.add(new Object[]{row.getTjrq(),row.getZh_sf(),row.getSjlx(),row.getTjsl(),row.getGxsj()});
        }
        jdbcTemplate.batchUpdate(SQL,argsList);
    }


    static int[] weight = { 7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2 }; // 十七位数字本体码权重
    static char[] validate = { '1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2' }; // mod11,对应校验码字符值
    static String cpResp = "0123456789ABCDEFGHJKLMNPQRSTUVWXYZ";
    static Random rd = new Random();
    // 得到校验码
    public static char getCheckoutCode(String Idnumber17) {
        int sum = 0; // wi*Ai和
        int mod = 0; // 进行模11运算
        try {
            for (int i = 0; i < 17; i++) {
                sum += Integer.parseInt(String.valueOf(Idnumber17.charAt(i)))
                        * weight[i];
            }
        } catch (Exception e) {
            return 'a';
        }
        mod = sum % 11;// 进行模11运算
        return validate[mod];// 返回校验码
    }

    public static void main(String[] args) {
        do{
//            List<Map<String, Object>> list = jdbcTemplate.query("select t.bm,t.sfzh from net_rural_jbtz_driver t where sfzh like '________******____' and rownum<=2000", new ColumnMapRowMapper());
            List<Map<String, Object>> list = jdbcTemplate.query("select bm,cp from net_rural_jbtz_car where cp like '%**%' and rownum<=2000", new ColumnMapRowMapper());

            if (CollectionUtils.isEmpty(list)) {
                return;
            }
            for (Map<String, Object> map : list) {
//                List<String> sfzhs = getSfzh(map.get("SFZH") + "");
                do{
//                    String sfzh = sfzhs.get(rd.nextInt(sfzhs.size()));
//                    try{
//                        jdbcTemplate.update("update net_rural_jbtz_driver set sfzh=? where bm=?",new Object[]{sfzh,map.get("BM")});
//                        jdbcTemplate.execute("commit");
//                        System.out.println("设置成功："+sfzh);
//                        break;
//                    }catch (Exception e){
//                        System.out.println("身份证号重复了："+sfzh);
//                    }
                    String cpx = String.valueOf(cpResp.charAt(rd.nextInt(cpResp.length())))+cpResp.charAt(rd.nextInt(cpResp.length()));
                    String cp = map.get("CP").toString().replace("**",cpx);
                    try{
                        jdbcTemplate.update("update net_rural_jbtz_car set cp=? where bm=?",new Object[]{cp,map.get("BM")});
                        jdbcTemplate.execute("commit");
                        System.out.println("设置成功："+cp);
                        break;
                    }catch (Exception e){
                        System.out.println("车牌重复了："+cp);
                    }


                }while (true);
            }


        }while (true);
    }

    public static List<String> getSfzh(String idNumber){
        int year = 1960+rd.nextInt(40);
        DateTime dt = new DateTime(year+1,1,1,0,0);
        DateTime temp = new DateTime(year,1,1,0,0);
        List<String> result = new ArrayList<>();
        do{
            String idNumber2 = idNumber.replace("******",temp.toString("yyMMdd"));
            String id17 = idNumber2.substring(0,17);
            char id1 = idNumber2.charAt(17);
            if(getCheckoutCode(id17) == id1) {
                result.add(idNumber2);
            }
            temp = temp.plusDays(1);
        }while (temp.isBefore(dt));
        return result;
    }
}
